package com.nvn.androidapp.adapters;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.nvn.androidapp.Objects.Document;
import com.nvn.androidapp.Objects.Picture;


public class DBAdapter {
	private static final String DATABASE_NAME = "Database_AndroidApp";
	private static final String prefix="c_";
	private static final String KEY_ID = prefix+"id";
	private static final String KEY_TYPE = prefix+"type";
	private static final String KEY_TITLE = prefix+"title";
	private static final String KEY_DES = prefix+"des";
	private static final String KEY_PRICE = prefix+"price";
	private static final String KEY_EXDATE = prefix+"exdate";
	private static final String KEY_CONTACT = prefix+"contact";
	
	private static final String KEY_THUM = prefix+"thum";
	private static final String KEY_PATH = prefix+"path";
	private static final String KEY_CAPTION = prefix+"caption";
	private static final String KEY_INDEX = prefix+"index";
	private static final String KEY_LEFT = prefix+"left";
	private static final String KEY_TOP = prefix+"top";
	private static final String KEY_BOT = prefix+"bot";
	private static final String KEY_RIGHT = prefix+"right";
	private static final String KEY_RULE1 = prefix+"rule1";
	private static final String KEY_RULE2 = prefix+"rule2";
	private static final String KEY_DEGREE = prefix+"degree";
	private static final String KEY_DOC_ID = prefix+"doc_id";
		
	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDB;
	private static final int DATABASE_VERSION = 1;
	private Context mContext;
	public static int count=0;
	/*
	 * tables
	 */

	private static final String TABLE_DOCUMENTS = prefix+"docs";
	private static final String TABLE_PICTURES = prefix+"pics";
	//create string
	private static final String CREATE_TABLE_DOCUMENTS="CREATE TABLE IF NOT EXISTS "+TABLE_DOCUMENTS+" ("+KEY_ID+" integer primary key AUTOINCREMENT not null, "
			+KEY_TYPE +" text not null, "
			+KEY_TITLE +" text not null, "
			+KEY_DES +" text not null, "
			+KEY_PRICE +" text not null, "
			+KEY_EXDATE +" text not null, "
			+KEY_CONTACT +" text not null, " 
			+KEY_THUM +" blob not null); ";
	private static final String CREATE_TABLE_PICTURES="CREATE TABLE IF NOT EXISTS "+TABLE_PICTURES+" ("+KEY_ID+" integer primary key AUTOINCREMENT not null, "
			+KEY_DOC_ID +" integer not null, "
			+KEY_PATH +" text not null, " 
			+KEY_CAPTION +" text not null, " 
			+KEY_INDEX+ " integer not null, " 
			+KEY_LEFT+ " integer not null, " 
			+KEY_TOP+ " integer not null, " 
			+KEY_BOT+ " integer not null, " 
			+KEY_RIGHT+ " integer not null, " 
			+KEY_RULE1+ " integer not null, " 
			+KEY_RULE2+ " integer not null, " 
			+KEY_DEGREE+ " integer not null); ";
	

	private class DatabaseHelper extends SQLiteOpenHelper{

		public DatabaseHelper(Context context, String name,CursorFactory factory, int version) {
			super(context, name, factory, version); 
		}
		@Override
		public void onCreate(SQLiteDatabase db){
			try{
				db.execSQL(CREATE_TABLE_DOCUMENTS);
				db.execSQL(CREATE_TABLE_PICTURES);
			}catch (Exception e) {e.printStackTrace();}
		}
		@Override
		public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {}

	}
	
	public DBAdapter(Context ctx){
		this.mContext = ctx;
	}
	public DBAdapter open () 
	{
		count++;
		Log.i("COUNT", count+"");
		if(mDB!=null && mDB.isOpen()){
			return this;
		}else{
			try{
				mDbHelper = new DatabaseHelper(mContext, DATABASE_NAME, null, DATABASE_VERSION);
				mDB = mDbHelper.getWritableDatabase();
			}catch(SQLException e){
				
			}
		}
		return this;
	}
	public void close(){
		count--;
		Log.i("COUNT", count+"");
		if(count==0) mDbHelper.close();
	}
	
	private boolean createTable(String tableName,String sqlCommand){
		open();
		try{
			mDB.execSQL("DROP TABLE IF EXISTS "+tableName);
			mDB.execSQL(sqlCommand);
			close();
			return true;
		}catch (Exception e) {close();return false;}
	}

	
	public int addDocument(Document doc){
		open();
		int idAdded=0;
		
		ContentValues inititalValues = new ContentValues();
		inititalValues.put(KEY_TYPE, doc.getType());
		inititalValues.put(KEY_TITLE, doc.getTitle());
		inititalValues.put(KEY_DES, doc.getDes());
		inititalValues.put(KEY_PRICE, doc.getPrice());
		inititalValues.put(KEY_EXDATE, doc.getExDate());
		inititalValues.put(KEY_CONTACT, doc.getContact());
		inititalValues.put(KEY_THUM, doc.getThumb());
		
		try {
			idAdded=(int)mDB.insert(TABLE_DOCUMENTS, null, inititalValues);
		} catch (Exception e) {
			e.printStackTrace();
		}
		close();
		return idAdded;
	}	
	
	public void updateDocument(Document doc){
		open();
		
		ContentValues inititalValues = new ContentValues();
		inititalValues.put(KEY_TYPE, doc.getType());
		inititalValues.put(KEY_TITLE, doc.getTitle());
		inititalValues.put(KEY_DES, doc.getDes());
		inititalValues.put(KEY_PRICE, doc.getPrice());
		inititalValues.put(KEY_EXDATE, doc.getExDate());
		inititalValues.put(KEY_CONTACT, doc.getContact());
		inititalValues.put(KEY_THUM, doc.getThumb());
		
		try {
			mDB.update(TABLE_DOCUMENTS, inititalValues, KEY_ID + "=" + doc.getID(), null);
		} catch (Exception e) {
			e.printStackTrace();
		}
		close();
	}
	
	public boolean delDocument(long id)
	{
		boolean value;
		open();
		value=mDB.delete(TABLE_DOCUMENTS, KEY_ID + "=" + id, null) >0;
		close();
		return value;
	}
	
	public void getAllDocuments(ArrayList<Document> docs){
		docs.clear();
		open();
		Cursor cursor = mDB.query(true, TABLE_DOCUMENTS, null, null, null, null, null, KEY_ID, null);
		if(cursor.moveToFirst())
		{
			do{
				Document doc= new Document(
						cursor.getInt(cursor.getColumnIndex(KEY_ID)),
						cursor.getString(cursor.getColumnIndex(KEY_TYPE)),
						cursor.getString(cursor.getColumnIndex(KEY_TITLE)),
						cursor.getString(cursor.getColumnIndex(KEY_DES)),
						cursor.getString(cursor.getColumnIndex(KEY_PRICE)),
						cursor.getString(cursor.getColumnIndex(KEY_CONTACT)),
						cursor.getString(cursor.getColumnIndex(KEY_EXDATE)),
						cursor.getBlob(cursor.getColumnIndex(KEY_THUM)));
				docs.add(doc);
			}while(cursor.moveToNext());
		}
		cursor.close();
		close();
	}
	
	public Document getDocByID(int id){
		Document doc = null;
		open();
		Cursor cursor = mDB.query(true, TABLE_DOCUMENTS, null, KEY_ID + "=" + id, null, null, null, null, null);
		if(cursor.moveToFirst()){
			doc= new Document(
				cursor.getInt(cursor.getColumnIndex(KEY_ID)),
				cursor.getString(cursor.getColumnIndex(KEY_TYPE)),
				cursor.getString(cursor.getColumnIndex(KEY_TITLE)),
				cursor.getString(cursor.getColumnIndex(KEY_DES)),
				cursor.getString(cursor.getColumnIndex(KEY_PRICE)),
				cursor.getString(cursor.getColumnIndex(KEY_CONTACT)),
				cursor.getString(cursor.getColumnIndex(KEY_EXDATE)),
				cursor.getBlob(cursor.getColumnIndex(KEY_THUM)));
		}
		cursor.close();
		close();
		return doc;
	}
	
	/**
	 * PICTURES
	 */
	public long addPicture(Picture pic){
		open();
		long idAdded=0;
		
		ContentValues inititalValues = new ContentValues();
		inititalValues.put(KEY_DOC_ID, pic.getDocID());
		inititalValues.put(KEY_PATH, pic.getPath());
		inititalValues.put(KEY_CAPTION, pic.getCaption());
		inititalValues.put(KEY_INDEX, pic.getIndex());
		inititalValues.put(KEY_LEFT, pic.getLeft());
		inititalValues.put(KEY_TOP, pic.getTop());
		inititalValues.put(KEY_BOT, pic.getBot());
		inititalValues.put(KEY_RIGHT, pic.getRight());
		inititalValues.put(KEY_RULE1, pic.getRule1());
		inititalValues.put(KEY_RULE2, pic.getRule2());
		inititalValues.put(KEY_DEGREE, pic.getDegree());
		
		try {
			idAdded=mDB.insert(TABLE_PICTURES, null, inititalValues);
		} catch (Exception e) {
			e.printStackTrace();
		}
		close();
		return idAdded;
	}	
	
	public void updatePicture(Picture pic){
		open();
		
		ContentValues inititalValues = new ContentValues();
		inititalValues.put(KEY_DOC_ID, pic.getDocID());
		inititalValues.put(KEY_PATH, pic.getPath());
		inititalValues.put(KEY_CAPTION, pic.getCaption());
		inititalValues.put(KEY_INDEX, pic.getIndex());
		inititalValues.put(KEY_LEFT, pic.getLeft());
		inititalValues.put(KEY_TOP, pic.getTop());
		inititalValues.put(KEY_BOT, pic.getBot());
		inititalValues.put(KEY_RIGHT, pic.getRight());
		inititalValues.put(KEY_RULE1, pic.getRule1());
		inititalValues.put(KEY_RULE2, pic.getRule2());
		inititalValues.put(KEY_DEGREE, pic.getDegree());
		
		try {
			mDB.update(TABLE_PICTURES, inititalValues, KEY_ID + "=" + pic.getID(), null);
		} catch (Exception e) {
			e.printStackTrace();
		}
		close();
	}
	
	public boolean delPicture(long id)
	{
		boolean value;
		open();
		value=mDB.delete(TABLE_PICTURES, KEY_ID + "=" + id, null) >0;
		close();
		return value;
	}
	
	public void getAllPictures(ArrayList<Picture> pics){
		pics.clear();
		open();
		Cursor cursor = mDB.query(true, TABLE_PICTURES, null, null, null, null, null, KEY_DOC_ID +","+ KEY_INDEX, null);
		if(cursor.moveToFirst())
		{
			do{
				Picture pic= new Picture(
						cursor.getInt(cursor.getColumnIndex(KEY_ID)),
						cursor.getInt(cursor.getColumnIndex(KEY_DOC_ID)),
						cursor.getString(cursor.getColumnIndex(KEY_PATH)),
						cursor.getString(cursor.getColumnIndex(KEY_CAPTION)),
						cursor.getInt(cursor.getColumnIndex(KEY_INDEX)),
						cursor.getInt(cursor.getColumnIndex(KEY_LEFT)),
						cursor.getInt(cursor.getColumnIndex(KEY_TOP)),
						cursor.getInt(cursor.getColumnIndex(KEY_BOT)),
						cursor.getInt(cursor.getColumnIndex(KEY_RIGHT)),
						cursor.getInt(cursor.getColumnIndex(KEY_RULE1)),
						cursor.getInt(cursor.getColumnIndex(KEY_RULE2)),
						cursor.getInt(cursor.getColumnIndex(KEY_DEGREE)));
				pics.add(pic);
			}while(cursor.moveToNext());
		}
		cursor.close();
		close();
	}
	
	public void getPicturesByDoc(ArrayList<Picture> pics, int docID){
		pics.clear();
		open();
		Cursor cursor = mDB.query(true, TABLE_PICTURES, null, KEY_DOC_ID + "=" + docID, null, null, null, KEY_INDEX, null);
		if(cursor.moveToFirst())
		{
			do{
				Picture pic= new Picture(
						cursor.getInt(cursor.getColumnIndex(KEY_ID)),
						cursor.getInt(cursor.getColumnIndex(KEY_DOC_ID)),
						cursor.getString(cursor.getColumnIndex(KEY_PATH)),
						cursor.getString(cursor.getColumnIndex(KEY_CAPTION)),
						cursor.getInt(cursor.getColumnIndex(KEY_INDEX)),
						cursor.getInt(cursor.getColumnIndex(KEY_LEFT)),
						cursor.getInt(cursor.getColumnIndex(KEY_TOP)),
						cursor.getInt(cursor.getColumnIndex(KEY_BOT)),
						cursor.getInt(cursor.getColumnIndex(KEY_RIGHT)),
						cursor.getInt(cursor.getColumnIndex(KEY_RULE1)),
						cursor.getInt(cursor.getColumnIndex(KEY_RULE2)),
						cursor.getInt(cursor.getColumnIndex(KEY_DEGREE)));
				pics.add(pic);
			}while(cursor.moveToNext());
		}
		cursor.close();
		close();
	}
}